package org.sunspotworld.demo;

import java.sql.*;
import java.io.*;

public class ClassDB {

    public static Connection con = null;
    public static Statement stmt = null;
    public static ResultSet rs = null;

    public ClassDB() {
    }

    public static Connection openConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException cE) {
            System.out.println("Class not found Excetion " + cE.getMessage());
        //System.out.println("Stack Trace " + cE.printStackTrace();

        }
        String server = "localhost";
        String port = "3306";
        String dbase = "rehab";
        String username = "root";
        String password = "123";
        String connectionUrl = "jdbc:mysql://" + server + ":" + port + "/" + dbase;
        System.out.println("URL = " + connectionUrl);
        try {

            con = DriverManager.getConnection(connectionUrl, username, password);
            System.out.println("URL = " + connectionUrl);
            System.out.print("Connection : " + con);
        } catch (SQLException e) {
            System.out.println("SQL connection exception" + e.getMessage());

        }
        if (con == null) {
            System.out.println("Creating connection failed");
        }
        return con;
    }

    public static void closeConnection(Connection con) {

        if (null != con) {
            try {
                con.close();
            } catch (SQLException sqExcep) {
                System.out.println("SQL Exception close connection" + sqExcep.toString());
            }
        }
    }

    public static void closeConnection() {

        if (null != con) {
            try {
                con.close();
            } catch (SQLException sqExcep) {
                System.out.println("SQL Exception close connection" + sqExcep.toString());
            }
        }
    }

    public static void addToaccreading(Connection conn, String id, double tMS, int index, double x, double y, double z, double g, boolean twoG) {

        Statement stmt = null;
        String sqlString = "INSERT INTO accreading VALUES('" + id + "'," + tMS + "," + index + "," + x + "," + y + "," + z + "," + g + "," + twoG + ")";
        try {
            if (conn == null) {
                System.out.println("Connection not returned \n");
            }

            stmt = conn.createStatement();
            stmt.executeUpdate(sqlString);
        } catch (Exception e) {
            System.out.println("SQL Exception adding to table " + e.toString());
        }
    // closing the connection


    }

    /**
     * This function adds a new patient to the system
     * @param name
     * @param pID
     */
    public static void addNewPatient(int pID, String name, String last, String gender, int age, String address, String city, String state ) {

        String sqlString = "INSERT INTO patients VALUES(" + pID + ",'" + name + "','"+ last + "','" +gender + "',"+ age + ",'" + address + "','"+city+"','" + state + "')";
        try {
            con = openConnection();
            stmt = con.createStatement();
            stmt.executeUpdate(sqlString);
        } catch (Exception e) {
            System.out.println("SQL Exception adding to table" + e.toString());
        }
        // closing the connection
        closeConnection(con);

    }

    public static void readFile(String filename) {
        Connection conn;
        Statement stmt;
        try {
            BufferedReader buf = new BufferedReader(new FileReader(filename));
            String line;
            conn = openConnection();


            while ((line = buf.readLine()) != null) {
                String words[] = line.split(",");
                System.out.println(words[0] + words[1] + words[2]);

                String sqlString = "INSERT INTO accReading VALUES (" + words[0] + "," + words[1] + "," + words[2] + ")";
                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                stmt.executeUpdate(sqlString);

            }
            conn.close();

        } catch (Exception e) {
            System.out.println("Exception reading file in DB" + e.getMessage());
        }
    }

    /** This method retrives a restultset from the database and displays it on the the scree
     * @param none
     */
    public static ResultSet retriveData() {

        String sqlString = "Select * from accReading";
        String result = null;

        try {
            con = openConnection();
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery(sqlString);
        } catch (SQLException e) {
            System.out.println("Reading from data base erro - " + e.getMessage());
        }

        return rs;

    }

    public static boolean authUser(String user, String pass) {
        boolean returnVal = false;
        ResultSet resultSet = null;
        Connection connection = null;
        Statement statement = null;
        System.out.print("user = "+user+"Pass = "+ pass);

        String sqlQuery = "SELECT uName, uPassword FROM users WHERE uName = '" + user+"'AND uPassword = '" + pass+"'";
        try {
            connection = openConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sqlQuery);
            if (resultSet == null) {
                returnVal = false;
            } else {
                returnVal = true;
            }

            System.out.println("User Verification = " + returnVal);

        } catch (SQLException e) {
            System.out.println("User verification error - " + e.getMessage());
        } finally {
            try {
                statement.close();
//                resultSet.close();
                connection.close();
            } catch (SQLException e) {
                System.out.println(" closing exception authUser " + e.getMessage());
                e.printStackTrace();
            }
        }
        return returnVal;
    }
}